package com.ytktt.study.week5.task10.repository;

import com.ytktt.study.week5.task10.entity.StudyEntity;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.List;

/**
 * Study仓库
 *
 * @author ytk
 */
@Slf4j
public class StudyRepository {

    private StudyRepository() {}

    private static class StudyRepositoryHolder {
        private static final StudyRepository HOLDER = new StudyRepository();
    }

    public static final StudyRepository getInstance() {
        return StudyRepositoryHolder.HOLDER;
    }

    public StudyEntity save(StudyEntity studyEntity, Connection connection) throws SQLException {
        String sql = "INSERT INTO tb_study(name) VALUES (?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            preparedStatement.setString(1, studyEntity.getName());
            int effect = preparedStatement.executeUpdate();
            if (effect == 1) {
                log.info("studyEntity: {} save success", studyEntity);
            } else {
                log.info("studyEntity: {} save err", studyEntity);
                throw new SQLDataException("save err");
            }

            try (ResultSet rs = preparedStatement.getGeneratedKeys()) {
                if (rs.next()) {
                    studyEntity.setId(rs.getInt(1));
                    return studyEntity;
                }
            }
            log.error("studyEntity: {} insert err by id autoincrement", studyEntity);
            throw new SQLDataException("id autoincrement err");
        }
    }

    public void updateById(StudyEntity studyEntity, Connection connection) throws SQLException {
        String sql = "UPDATE tb_study SET name = ? WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, studyEntity.getName());
            preparedStatement.setInt(2, studyEntity.getId());
            int effect = preparedStatement.executeUpdate();
            if (effect == 1) {
                log.info("studyEntity: {} update success", studyEntity);
            } else {
                log.info("studyEntity: {} update err", studyEntity);
                throw new SQLDataException("update err");
            }
        }
    }

    public StudyEntity getById(Integer id, Connection connection) throws SQLException {
        String sql = "SELECT id, name FROM tb_study WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, id);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    StudyEntity studyEntity = new StudyEntity();
                    studyEntity.setId(resultSet.getInt("id"));
                    studyEntity.setName(resultSet.getString("name"));
                    return studyEntity;
                }
            }
        }
        return null;
    }

    public void deleteById(Integer id, Connection connection) throws SQLException {
        String sql = "DELETE FROM tb_study WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setInt(1, id);
            int effect = preparedStatement.executeUpdate();
            if (effect == 1) {
                log.info("studyEntity id: {} delete success", id);
            } else {
                log.info("studyEntity id: {} delete err", id);
                throw new SQLDataException("delete err");
            }
        }
    }

    /**
     * 批量插入 也可以使用INSERT INTO tb_study(name) VALUES (?, ?, ?...)的方式
     * @param list
     * @param connection
     * @throws SQLException
     */
    public void saveBatch(List<StudyEntity> list, Connection connection) throws SQLException {
        String sql = "INSERT INTO tb_study(name) VALUES (?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (StudyEntity studyEntity : list) {
                preparedStatement.setString(1, studyEntity.getName());
                preparedStatement.addBatch();
            }
            int[] results = preparedStatement.executeBatch();
            for (int i = 0; i < results.length; i++) {
                if (results[i] != 1) {
                    log.info("studyEntity: {} save err", list.get(i));
                }
            }
        }
    }
}
